package zozopo.flow.operations;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import zozopo.database.BusiDatabaseManager;
import zozopo.operations.comm.OperatingService;
import zozopo.warehouse.operations.GoodsTB;

public class FlowOS  extends OperatingService{
	public FlowOS(Connection conn){
		super(conn);
	}
	
	public FlowOS(){
		this.databaseConnection = new BusiDatabaseManager(); //过期
		this.conn = databaseConnection.getConnection();
	}
	
	/**
	 * @version 1.0
	 * @see 
	 */
	public List<FlowTB> selectListBySql(String sql){
		List<FlowTB> flowList = null;
		ResultSet resultSet = databaseConnection.executeQuery(conn, sql);
		
		if(resultSet != null){
				try {
					flowList = new ArrayList<FlowTB>();
					while(resultSet.next()){
						FlowTB flow = new FlowTB();
						
						flow.setId_flow(resultSet.getInt(FlowTB.ID_FLOW));
						
						flow.setCnname_flow(resultSet.getString(FlowTB.CNNAME_FLOW));
						flow.setCode_flow(resultSet.getString(FlowTB.CODE_FLOW));
						
						flow.setEnname_flow(resultSet.getString(FlowTB.ENNAME_FLOW));
						flow.setFunction_flow(resultSet.getString(FlowTB.FUNCTION_FLOW));
						flow.setKind_flow(resultSet.getString(FlowTB.KIND_FLOW));
						flow.setRemark_flow(resultSet.getString(FlowTB.REMARK_FLOW));
						flow.setValue_flow(resultSet.getInt(FlowTB.VALUE_FLOW));
						flow.setNumber_flow(resultSet.getInt(FlowTB.NUMBER_FLOW));
						
						flow.setNextvalue_flow(resultSet.getInt(FlowTB.NEXTVALUE_FLOW));

						flowList.add(flow);
					}
					resultSet.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
		}
		
		return flowList;
	
	}
	
	/**
	 * @version 1.0
	 * @see 
	 */
	public int getRowNum(){
		
		int rowNum = 0;
		String sql = "select count(*) as rowNum from " + FlowTB.TABLE_NAME ;
		
		ResultSet resultSet = databaseConnection.executeQuery(conn, sql);
		if(resultSet != null){
			try {
				
				while(resultSet.next()){
					rowNum = resultSet.getInt("rowNum");
				}
				resultSet.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		
		return rowNum;
	}
	
	/**
	 * @version 1.0
	 * @author Shea
	 */
	public List<FlowTB> selectBySearchStr(String searchStr,int beginPageNum,int pageSize){
		String sql = "select * from "+GoodsTB.TABLE_NAME+" WHERE  "
		+GoodsTB.NAME_GOODS+" LIKE '%"+searchStr+"%'"
		+" OR "+GoodsTB.ID_GOODS+" LIKE '%"+searchStr+"%'"
		+" OR "+GoodsTB.CNSPELL_GOODS+" LIKE '%"+searchStr+"%'"
		+" OR "+GoodsTB.FWCNSPELL_GOODS+" LIKE '%"+searchStr+"%'"
		+" limit "+beginPageNum+","+pageSize; 
		//ResultSet resultSet = databaseConnection.executeQuery(conn, sql);
		return selectListBySql(sql);
	}
	/**
	 * @version 1.0
	 * @see 
	 */
	public int getRowNumForSearchStr(String searchStr){
		
		int rowNum = 0;
		String sql = "select count(*) as rowNum from " + GoodsTB.TABLE_NAME +" WHERE  "
		+GoodsTB.NAME_GOODS+" LIKE '%"+searchStr+"%'"
		+" OR "+GoodsTB.ID_GOODS+" LIKE '%"+searchStr+"%'"
		+" OR "+GoodsTB.CNSPELL_GOODS+" LIKE '%"+searchStr+"%'"
		+" OR "+GoodsTB.FWCNSPELL_GOODS+" LIKE '%"+searchStr+"%'"; 
		
		ResultSet resultSet = databaseConnection.executeQuery(conn, sql);
		if(resultSet != null){
			try {
				
				while(resultSet.next()){
					rowNum = resultSet.getInt("rowNum");
				}
				resultSet.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		
		return rowNum;
	}
	
	public List<FlowTB> selectAll(int beginPageNum,int pageSize){
		
		List<FlowTB> allList = null;
		String sql = "SELECT * FROM " + FlowTB.TABLE_NAME  +" ORDER BY "+FlowTB.ID_FLOW+" DESC limit "+beginPageNum+","+pageSize; 
		allList = selectListBySql(sql);
		
		return allList;
	}
	
	public List<FlowTB> selectAll(){
		
		List<FlowTB> allList = null;
		String sql = "SELECT * FROM " + FlowTB.TABLE_NAME  +" ORDER BY "+FlowTB.ID_FLOW+" DESC "; 
		allList = selectListBySql(sql);
		
		return allList;
	}
	
	/**
	 * @version 1.0
	 * @author Shea
	 */
	public List<FlowTB> getFlowListByKind(String kind){
		String sql = "SELECT * FROM " + FlowTB.TABLE_NAME  +" ORDER BY "+FlowTB.VALUE_FLOW+" DESC "; 
		
		List<FlowTB> flowList = new ArrayList<FlowTB>();
		flowList = selectListBySql(sql);
		return flowList;
	}
	
	
	
	/**
	 * @version 1.0
	 * @author gee
	 */
	public FlowTB getFlow(String code){
		String sql = "SELECT * FROM " + FlowTB.TABLE_NAME  
		+ " WHERE " + FlowTB.CODE_FLOW + "='" + code +"'"
		+" ORDER BY "+FlowTB.VALUE_FLOW+" DESC "; 
		FlowTB flow = selectListBySql(sql).get(0);
		return flow;
	}
	
	

	
	

	
	
	/**
	 * @version 1.0
	 * @author Shea
	 */
	public Map<String,FlowTB> getFlowCodeMapByKind(String kind){
		String sql = "SELECT * FROM " + FlowTB.TABLE_NAME  
		+ " WHERE " + FlowTB.KIND_FLOW + "='" + kind + "'"
		+" ORDER BY "+FlowTB.VALUE_FLOW+" DESC "; 
		List<FlowTB> flowList = new ArrayList<FlowTB>();
		flowList = selectListBySql(sql);

		Map<String,FlowTB> flowCodeMap = new HashMap<String,FlowTB>();
		
		for(FlowTB flow:flowList){
			flowCodeMap.put(flow.getCode_flow(),flow);
		}
		
		return flowCodeMap;
		
	}
	
		
	
	/**
	 * @version 1.0
	 * @author Shea
	 */
	public Map<Integer,List<FlowTB>> getFlowValueMapByKind(String kind){
		String sql = "SELECT * FROM " + FlowTB.TABLE_NAME  
		+ " WHERE " + FlowTB.KIND_FLOW + "='" + kind + "'"
		+" ORDER BY "+FlowTB.VALUE_FLOW+" DESC "; 
		
		List<FlowTB> flowList = new ArrayList<FlowTB>();
		flowList = selectListBySql(sql);
		 
		
		
		Map<Integer,List<FlowTB>> flowValueMap = new HashMap<Integer,List<FlowTB>>();
		
		List<FlowTB> flowValueList = new ArrayList<FlowTB>();
		int flowValue = -1;
		//int lastFlowValue = -1;
		
		for(FlowTB flow:flowList){
		//	
			if(flowValue != -1 && flowValue != flow.getValue_flow()){
				flowValueMap.put(flowValue,flowValueList);
				flowValueList = new ArrayList<FlowTB>();
				flowValueList.add(flow);
				flowValue = flow.getValue_flow();
			}else if(flowValue != flow.getValue_flow()){
				flowValueList = new ArrayList<FlowTB>();
				flowValueList.add(flow);
				flowValue = flow.getValue_flow();
			}else if(flowValue == flow.getValue_flow()){
				flowValueList.add(flow);
				flowValue = flow.getValue_flow();
			}
			if(flow.getValue_flow() == -1){
				flowValueMap.put(flowValue,flowValueList);
				flowValueList = new ArrayList<FlowTB>();
				flowValueList.add(flow);
				flowValue = flow.getValue_flow();
			}
		}
		
		flowValueMap.put(flowValue,flowValueList);//修改正最后一次加入
		
		return flowValueMap;
	}
	
	
	/**
	 * @version 1.0
	 * @author gee
	 */
	public boolean add(String code){
		String sql = "UPDATE flow SET number_flow = number_flow+1 WHERE code_flow='"+code+"'";
		
		return databaseConnection.executeUpdate(conn, sql);
	}
	
	/**
	 * @version 1.0
	 * @author gee
	 */
	public boolean reduce(String code){
		String sql = "UPDATE flow SET number_flow = number_flow-1 WHERE code_flow='"+code+"'";
		 
		
		//conn.prepareStatement(sql);
		
		return databaseConnection.executeUpdate(conn, sql);
	}
	
	
	/**
	 * @version 1.0
	 * @author gee
	 */
	public int getNumberByFlowCode(String code){
		int rowNum = 0;
		
		String sql = "SELECT COUNT(*) AS rowNum " +
		"FROM receiptlist_warehouse  " +
		" WHERE id_receiptlist " + 
		" IN ( SELECT kindid_recordlist " +
		"FROM recordlist_flow WHERE code_recordlist='" +
		code+ "' AND flowflag_recordlist=1 ) " +
		"AND state_receiptlist!=9"; 
		
		ResultSet resultSet = databaseConnection.executeQuery(conn, sql);
		if(resultSet != null){
			try {
				
				while(resultSet.next()){
					rowNum = resultSet.getInt("rowNum");
				}
				resultSet.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		
		return rowNum;
	}
	
}
